package com.demoweb.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.demoweb.model.dto.UploadFile;

public class UploadFileDao {

	public void insertUploadFile(UploadFile file) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			String sql = 
				"INSERT INTO uploadfile " + 
				"(uploadno, savedfilename, userfilename) " +
				"VALUES (?, ?, ?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, file.getUploadNo());
			pstmt.setString(2, file.getSavedFileName());
			pstmt.setString(3, file.getUserFileName());
			pstmt.executeUpdate();
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try { pstmt.close(); } catch (Exception ex) { }
			try { conn.close(); } catch (Exception ex) { }
		}		
		
	}

	public ArrayList<UploadFile> getUploadFilesByUploadNo(int uploadNo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<UploadFile> files = new ArrayList<UploadFile>();
		
		try {
			conn = ConnectionHelper.getConnection();
			String sql = 
				"SELECT uploadfileno, uploadno, savedfilename, userfilename " + 
				"FROM uploadfile WHERE uploadfileno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, uploadNo);
			rs = pstmt.executeQuery();
				
			while (rs.next()) {
				UploadFile file = new UploadFile();
				file.setUploadFileNo(rs.getInt(1));
				file.setUploadNo(rs.getInt(2));
				file.setSavedFileName(rs.getString(3));
				file.setUserFileName(rs.getString(4));
				files.add(file);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try { rs.close(); } catch (Exception ex) {}
			try { pstmt.close(); } catch (Exception ex) {}
			try { conn.close(); } catch (Exception ex) {}
		}
		return files;
		
	}
	
	public UploadFile getUploadFileByUploadFileNo(int uploadFileNo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		UploadFile file = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			String sql = 
				"SELECT uploadfileno, uploadno, savedfilename, userfilename " + 
				"FROM uploadfile WHERE uploadfileno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, uploadFileNo);
			rs = pstmt.executeQuery();
				
			if (rs.next()) {
				file = new UploadFile();
				file.setUploadFileNo(rs.getInt(1));
				file.setUploadNo(rs.getInt(2));
				file.setSavedFileName(rs.getString(3));
				file.setUserFileName(rs.getString(4));
				
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try { rs.close(); } catch (Exception ex) {}
			try { pstmt.close(); } catch (Exception ex) {}
			try { conn.close(); } catch (Exception ex) {}
		}
		return file;
		
	}
	
	public void increaseUploadReadCount(int uploadNo) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			String sql = 
				"UPDATE upload " + 
				"SET readcount = readcount + 1 " +
				"WHERE uploadno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, uploadNo);
			pstmt.executeUpdate();
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try { pstmt.close(); } catch (Exception ex) { }
			try { conn.close(); } catch (Exception ex) { }
		}		
		
	}
	
	public void increaseUploadFileDownloadCount(int uploadFileNo) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			String sql = 
				"UPDATE uploadfile " + 
				"SET downloadcount = downloadcount + 1 " +
				"WHERE uploadfileno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, uploadFileNo);
			pstmt.executeUpdate();
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try { pstmt.close(); } catch (Exception ex) { }
			try { conn.close(); } catch (Exception ex) { }
		}		
		
	}
	
}











